﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
using System.Text;
using ManualSymbolStackTrace;
using Microsoft.Samples.Debugging.CorSymbolStore;
using PropertyExpression.Common;

namespace PropertyExpression.ControllableQuery.Toolkit
{
    public abstract class QLocation
    {
        public class Class : QLocation
        {
            public readonly IQuery Query;

            public Class(IQuery query)
            {
                Query = query;
            }

            public override T Match<T>(Func<Class, T> @class, Func<Method, T> method, Func<NonDisplay, T> nonDisplay,
                Func<StringQuery, T> stringQuery)
            {
                return @class(this);
            }
        }

        public class Method : QLocation
        {
            public readonly MethodInfo MethodInfo;

            public Method(MethodInfo methodInfo)
            {
                MethodInfo = methodInfo;
            }

            public override T Match<T>(Func<Class, T> @class, Func<Method, T> method, Func<NonDisplay, T> nonDisplay,
                Func<StringQuery, T> stringQuery)
            {
                return method(this);
            }
        }

        public class NonDisplay : QLocation
        {
            public override T Match<T>(Func<Class, T> @class, Func<Method, T> method, Func<NonDisplay, T> nonDisplay,
                Func<StringQuery, T> stringQuery)
            {
                return nonDisplay(this);
            }
        }

        public abstract T Match<T>(Func<Class, T> @class, Func<Method, T> method, Func<NonDisplay, T> nonDisplay,
            Func<StringQuery, T> stringQuery);

        public class StringQuery : QLocation
        {
            public readonly string FilePath;
            public readonly int LineNumber;

            public StringQuery(string filePath, int lineNumber)
            {
                FilePath = filePath;
                LineNumber = lineNumber;
            }

            public override T Match<T>(Func<Class, T> @class, Func<Method, T> method, Func<NonDisplay, T> nonDisplay,
                Func<StringQuery, T> stringQuery)
            {
                return stringQuery(this);
            }

            public string FullName
            {
                get { return FilePath + " (Ln " + LineNumber + ")"; }
            }
        }
    }

    public interface IQRepository
    {
        void Add(SqlCommand sqlCommand, QLocation qLocation);
    }

    public class DoNothingQRepository : IQRepository
    {
        public void Add(SqlCommand sqlCommand, QLocation qLocation)
        {
            //no-op
        }
    }

    public class FindUsagesQRepository : IQRepository
    {
        private readonly Dictionary<string, HashSet<string>> queries = new Dictionary<string, HashSet<string>>();

        public void Add(SqlCommand sqlCommand, QLocation qLocation)
        {
            if (sqlCommand.CommandType == CommandType.StoredProcedure) return;
            var builder = BuildParams(sqlCommand);
            var methodInfo = qLocation.Match(
                @class => { throw new NotImplementedException(); },
                method => QLocationExtensions.GetQLocationString(method.MethodInfo),
                nonDisplay => { throw new NotImplementedException(); },
                stringQuery => stringQuery.FullName);
            HashSet<string> hashSet;
            if (!queries.TryGetValue(methodInfo, out hashSet))
            {
                hashSet = new HashSet<string>();
                queries.Add(methodInfo, hashSet);
            }
            if (builder.Length != 0) builder.AppendLine();
            hashSet.Add(string.Format(@"{0}AS 
    BEGIN
{1}
    END",
                                      builder,
                                      sqlCommand.CommandText));
        }

        internal static StringBuilder BuildParams(SqlCommand sqlCommand)
        {
            var builder = new StringBuilder();
            foreach (SqlParameter sqlParameter in sqlCommand.Parameters)
            {
                if (builder.Length > 0) builder.AppendLine(",");
                builder.Append(sqlParameter.ParameterName + " " + GetSqlTypeString(sqlParameter));
            }
            return builder;
        }

        public IEnumerable<string> FindUsages(string connectionString, string tableName, Option<string> columnName = new Option<string>())
        {
            var procedureNamePrefix = Guid.NewGuid().ToString("N");
            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();
                foreach (var query in queries)
                {
                    foreach (var procedureBody in query.Value)
                    {
                        DropProcedure(connection, procedureNamePrefix);

                        using (var command = new SqlCommand())
                        {
                            command.Connection = connection;

                            command.CommandText = string.Format(@"CREATE PROCEDURE ControllableQueryTemp{0}
{1}",
                                procedureNamePrefix,
                                procedureBody);
                            command.ExecuteNonQuery();
                        }

                        object result;
                        using (var command = new SqlCommand())
                        {
                            command.Connection = connection;
                            var builder = new StringBuilder();
                            builder.AppendFormat(@"SELECT  1
FROM    sys.dm_sql_referenced_entities('dbo.ControllableQueryTemp{0}', 'OBJECT')
WHERE   referenced_entity_name = @referenced_entity_name", procedureNamePrefix);
                            if (columnName.HasValue)
                            {
                                builder.Append(@"
        AND referenced_minor_name = @referenced_minor_name");
                                command.Parameters.Add(new SqlParameter
                                {
                                    SqlDbType = SqlDbType.NVarChar,
                                    Size = -1,
                                    ParameterName = "@referenced_minor_name",
                                    Value = columnName.Value
                                });
                            }
                            else builder.Append(@"
        AND referenced_minor_name IS NULL");
                            command.Parameters.Add(new SqlParameter
                            {
                                SqlDbType = SqlDbType.NVarChar,
                                Size = -1,
                                ParameterName = "@referenced_entity_name",
                                Value = tableName
                            });
                            command.CommandText = builder.ToString();
                            result = command.ExecuteScalar();
                        }

                        DropProcedure(connection, procedureNamePrefix);

                        if (result != null)
                        {
                            yield return query.Key;
                            break;
                        }
                    }
                }
            }
        }

        private static void DropProcedure(SqlConnection connection, string procedureNamePrefix)
        {
            using (var command = new SqlCommand())
            {
                command.Connection = connection;

                command.CommandText = string.Format(@"IF EXISTS ( SELECT  *
            FROM    sys.objects
            WHERE   object_id = OBJECT_ID(N'[dbo].[ControllableQueryTemp{0}]')
                    AND type IN ( N'P', N'PC' ) ) 
    DROP PROCEDURE [dbo].[ControllableQueryTemp{0}]", procedureNamePrefix);
                command.ExecuteNonQuery();
            }
        }

        private static string GetSqlTypeString(SqlParameter sqlParameter)
        {
            switch (sqlParameter.SqlDbType)
            {
                case SqlDbType.UniqueIdentifier:
                    return "UNIQUEIDENTIFIER";
                case SqlDbType.Int:
                    return "INT";
                case SqlDbType.NVarChar:
                    if (sqlParameter.Size == -1)
                        return "NVARCHAR(MAX)";
                    else
                        throw new ApplicationException();
                case SqlDbType.DateTime:
                    return "DATETIME";
                case SqlDbType.Decimal:
                    //TODO:
                    return "DECIMAL";
                case SqlDbType.Structured:
                    return sqlParameter.TypeName + " READONLY";
                default:
                    throw new ApplicationException();
            }
        }
    }

    public static class QLocationExtensions
    {
        public static string GetQLocationString(MethodBase methodInfo)
        {
            Option<SourceLocation> sourceLocation;
            try
            {
                sourceLocation = new StackTraceSymbolProvider(
                    methodInfo.ReflectedType.Assembly.Location,
                    SymSearchPolicies.AllowOriginalPathAccess |
                    SymSearchPolicies.AllowReferencePathAccess |
                    SymSearchPolicies.AllowRegistryAccess |
                    SymSearchPolicies.AllowSymbolServerAccess)
                    .GetSourceLocation(methodInfo);
            }
            catch
            {
                return DefaulttQLocationString(methodInfo);
            }
            return sourceLocation.Match(
                _ => string.Format("{0}:(Ln {1}, Col {2})",
                                   sourceLocation.Value.FilePath,
                                   sourceLocation.Value.Line,
                                   sourceLocation.Value.Column),
                () => DefaulttQLocationString(methodInfo));
        }

        private static string DefaulttQLocationString(MethodBase methodInfo)
        {
            return string.Format("{0}, {1}", methodInfo.DeclaringType, methodInfo.Name);
        }
    }

    internal class SourceLocation
    {
        public readonly int Line;
        public readonly int Column;
        public readonly string FilePath;

        public SourceLocation(int line, int column, string filePath)
        {
            Line = line;
            Column = column;
            FilePath = filePath;
        }
    }
}
